import os
import mysql.connector
import json
from datetime import datetime
import pandas as pd

def report_long_span_chats(min_weeks=4):
    """生成跨度超过指定周数的客户和群组的报告"""
    # 数据库配置
    db_config = {
        'host': os.getenv('MYSQL_HOST', 'localhost'),
        'user': os.getenv('MYSQL_USER', 'root'),
        'password': os.getenv('MYSQL_PASSWORD', 'neo..6555894'),
        'database': os.getenv('MYSQL_DATABASE', 'sales_chat_mvp')
    }
    
    try:
        # 连接数据库
        conn = mysql.connector.connect(**db_config)
        cursor = conn.cursor(dictionary=True)
        
        # 查询所有聊天记录
        query = """
        SELECT 
            nickname,
            is_group,
            customer_nickname,
            week_start_time,
            time_span
        FROM 
            chat_history
        ORDER BY 
            nickname, week_start_time
        """
        
        cursor.execute(query)
        records = cursor.fetchall()
        
        if not records:
            print("数据库中没有聊天记录")
            return
        
        # 按客户/群组名称分组
        chats_by_name = {}
        for record in records:
            name = record['nickname']
            if name not in chats_by_name:
                chats_by_name[name] = []
            chats_by_name[name].append(record)
        
        # 找出跨多周的客户
        long_span_chats = {}
        for name, chats in chats_by_name.items():
            if len(chats) >= min_weeks:
                long_span_chats[name] = chats
        
        # 打印结果
        if not long_span_chats:
            print(f"没有找到跨{min_weeks}周或以上的客户")
            return
        
        print(f"找到 {len(long_span_chats)} 个跨{min_weeks}周或以上的客户/群组：")
        print("=" * 80)
        
        # 按跨度周数排序
        sorted_chats = sorted(long_span_chats.items(), key=lambda x: len(x[1]), reverse=True)
        
        for name, chats in sorted_chats:
            is_group = chats[0]['is_group']
            chat_type = "群组" if is_group else "客户"
            customer_nickname = chats[0]['customer_nickname'] if not is_group else ""
            
            print(f"{chat_type}: {name}")
            if customer_nickname:
                print(f"客户微信昵称: {customer_nickname}")
            print(f"聊天记录跨越 {len(chats)} 周")
            
            # 获取最早和最晚的时间
            start_times = []
            end_times = []
            for chat in chats:
                time_span = json.loads(chat['time_span'])
                start_times.append(time_span['start_time'])
                end_times.append(time_span['end_time'])
            
            earliest = min(start_times)
            latest = max(end_times)
            
            print(f"总时间跨度: {earliest} 至 {latest}")
            print("-" * 80)
        
    except Exception as e:
        print(f"查询数据库时出错: {e}")
    
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

if __name__ == "__main__":
    report_long_span_chats(min_weeks=4) 